package com.huidao.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.huidao.entity.Goods;
import com.huidao.entity.GoodsType;
import com.huidao.entity.User;
import com.huidao.util.JDBCUtil;

public class GoodsDaoImp implements GoodsDao {
	private static Connection conn = null;
	private static PreparedStatement ps = null;
	private static ResultSet rs = null;

	@Override
	public List<Goods> getListGoods(String keyName, int currenpage,
			int pagesize, long gid) {
		List<Goods> list = new ArrayList<Goods>();
		String sql = "SELECT * FROM GOODS A LEFT JOIN GOODSTYPE B ON A.GOODSTYPE_ID = B.GOODSTYPE_ID  LEFT JOIN USER C ON A.USER_ID = C.USER_ID  WHERE 1=1";
		if (keyName != null && !keyName.trim().equals("")) {
			sql = sql + "  AND goods_name LIKE '%" + keyName + "%'";
		}
		if (gid != 0) {
			sql = sql + " AND goods_id = ' " + gid + " ' ";
		}
		sql = sql + " LIMIT ?,?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (currenpage - 1) * pagesize);
			ps.setInt(2, pagesize);
			rs = ps.executeQuery();
			while (rs.next()) {
				Goods goods = new Goods();
				GoodsType gt = new GoodsType();
				User user = new User();
				goods.setGid(rs.getLong("goods_id"));
				goods.setgName(rs.getString("goods_name"));
				goods.setgPrice(rs.getDouble("goods_price"));
				goods.setgAccount(rs.getInt("goods_account"));
				goods.setgShelves(rs.getInt("goods_shelves"));
				gt.setGoodstype_name(rs.getString("goodstype_name"));
				goods.setGoodsType(gt);
				user.setUserShopName(rs.getString("user_shopname"));
				goods.setUser(user);
				list.add(goods);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}
		return list;
	}


	@Override
	public int total(String keyName, long gid) {
		String sql = "SELECT COUNT(*) FROM GOODS WHERE 1=1";
		if (keyName != null && !keyName.trim().equals("")) {
			sql = sql + "  AND goods_name LIKE '%"+ keyName +"%'";
		}
		if (gid != 0) {
			sql = sql + "  AND goods_id = ' " + gid + " ' ";
		}
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}
		return 0;
	}

	@Override
	public boolean addGoods(Goods goods) {
		String sql = "INSERT INTO GOODS (GOODS_NAME,GOODS_PRICE,GOODS_ACCOUNT,GOODSTYPE_ID,USER_ID) VALUES(?,?,?,?,?)";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, goods.getgName());
			ps.setDouble(2, goods.getgPrice());
			ps.setInt(3, goods.getgAccount());
			ps.setLong(4, goods.getGoodsType().getGoodstype_id());
			ps.setLong(5, goods.getUser().getUserId());
			int i = ps.executeUpdate();
			if (i > 0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean delGoods(long gid) {
		String sql = "DELETE FROM GOODS WHERE GOODS_ID=?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, gid);
			int i = ps.executeUpdate();
			if (i > 0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean updateGoodsStatus(Goods goods) {
		String sql = "UPDATE GOODS SET GOODS_SHELVES =? WHERE GOODS_ID=? ";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			if (goods.getgShelves() == 1) {
				ps.setLong(1, 0);
			} else {
				ps.setLong(1, 1);
			}
			ps.setLong(2, goods.getGid());
			int i = ps.executeUpdate();
			if (i > 0) {
				return true;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean updateGoods(Goods goods) {
		String sql = "UPDATE GOODS SET GOODS_NAME =?,GOODS_PRICE=?,GOODS_ACCOUNT=?,GOODSTYPE_ID =? WHERE GOODS_ID=? ";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, goods.getgName());
			ps.setDouble(2, goods.getgPrice());
			ps.setInt(3, goods.getgAccount());
			ps.setLong(4, goods.getGoodsType().getGoodstype_id());
			ps.setLong(5, goods.getGid());
			int i = ps.executeUpdate();
			if (i > 0) {
				return true;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}


	@Override
	public boolean isExitGoods(Goods goods) {
		String sql = "SELECT *FROM GOODS WHERE GOODS_NAME=?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, goods.getgName());
			rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	
}
